#region Using directives
using System;
using System.Data;
using WP.DB;
using WP.Commons;
using System.Windows.Forms; 
#endregion

namespace WP.MF.DataAccess
{
    public class MPurgeDataDAL
    {
        public bool mblnIsRestorehistory = false;
        public ProgressBar progBar = null;
        public Label lblProccedure = null;
        public bool mblnMakeHistory = false;
        public bool PurgeOE(string strOrderCondition)
        {

            DBAccess dbcon = new DBAccess(false, false);
            string strSql = "";

            const string MoveOrderHdrtable = "OeOrderhdr_Hist";
            const string MoveOrderDtltable = "OeOrderdtl_Hist";
            const string MoveOrderTallytable = "OeOrdertally_Hist";
            const string MoveTruchChargetable = "oeTruckCharge_Hist";
            const string MovExtraCosttable = "OeExtraCost_Hist";
            const string MoveArSlspHisttable = "arSlspHist_Hist";
            const string MoveOeSalespersontable = "OeSalesperson_Hist";
            const string MoveOrderKittable = "OeOrderKit_Hist";
            if (mblnMakeHistory)
            {

                CreateHistTable(dbcon, "oeorderhdr", MoveOrderHdrtable);
                CreateHistTable(dbcon, "OeOrderdtl", MoveOrderDtltable);
                CreateHistTable(dbcon, "OeOrdertally", MoveOrderTallytable);
                CreateHistTable(dbcon, "OeTruckCharge", MoveTruchChargetable);
                CreateHistTable(dbcon, "OeExtraCost", MovExtraCosttable);
                CreateHistTable(dbcon, "arSlspHist", MoveArSlspHisttable);
                CreateHistTable(dbcon, "OeSalesperson", MoveOeSalespersontable);
                CreateHistTable(dbcon, "OeOrderdtl", MoveOrderDtltable);
                CreateHistTable(dbcon, "OeOrderKit", MoveOrderKittable);
            }
            DataView dvOrder = dbcon.GetDataTable("select OrdNo from OeOrderhdr where posted=1 and " + strOrderCondition).DefaultView;
            if (progBar != null)
            {
                progBar.Maximum = dvOrder.Count + 1;
                progBar.Minimum = 0;
                progBar.Value = 0;
                progBar.Visible = true;
            }

            string strOeorderhdrfield = GetTableFileds(dbcon, "OeOrderhdr");
            string stroeOrderdtlField = GetTableFileds(dbcon, "OeOrderdtl");
            string strOeOrdertallyField = GetTableFileds(dbcon, "OeOrderTally");
            string strOeOrderKitField = GetTableFileds(dbcon, "OeOrderKit");

            string stroeTruckChargeField = GetTableFileds(dbcon, "oeTruckCharge");
            string strOeExtraCostField = GetTableFileds(dbcon, "OeExtraCost");
            string strarSlspHistField = GetTableFileds(dbcon, "arSlspHist");
            string strOeSalesPersonField = GetTableFileds(dbcon, "OeSalesPerson");
            string strCondition, strCondition2, strCondition3;
            string strPct = "0/" + dvOrder.Count.ToString();
            lblProccedure.Text = strPct;
            lblProccedure.Refresh();
            Application.DoEvents();  
            int j = 0;

            foreach (DataRowView drw in dvOrder)
            {
                try
                {
                    dbcon.BeginTran();
                    if (mblnIsRestorehistory)
                    {
                        #region restore hisotry
                        strCondition = "InvNo='" + drw["OrdNo"].ToString() + "'";
                        strCondition2 = "OrdNo='" + drw["OrdNo"].ToString() + "'";
                        strSql = "Insert into oeorderhdr  (" + strOeorderhdrfield + ")" +
                                 "select  " + strOeorderhdrfield + " from " + MoveOrderHdrtable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveOrderHdrtable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        strSql = "Insert into OeOrderdtl  (" + stroeOrderdtlField + ")" +
                                 "select  " + stroeOrderdtlField + " from " + MoveOrderDtltable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveOrderDtltable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);


                        strSql = "Insert into OeOrdertally  (" + strOeOrdertallyField + ")" +
                                 "select  " + strOeOrdertallyField + " from " + MoveOrderTallytable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveOrderTallytable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);


                        strSql = "Insert into OeOrderKit  (" + strOeOrderKitField + ")" +
                                 "select  " + strOeOrderKitField + " from " + MoveOrderKittable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveOrderKittable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);


                        strSql = "Insert into oeTruckCharge  (" + stroeTruckChargeField + ")" +
                                 "select  " + stroeTruckChargeField + " from " + MoveTruchChargetable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveTruchChargetable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);


                        strSql = "Insert into OeExtraCost  (" + strOeExtraCostField + ")" +
                                 "select  " + strOeExtraCostField + " from " + MovExtraCosttable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MovExtraCosttable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);


                        strSql = "Insert into arSlspHist  (" + strarSlspHistField + ")" +
                                 "select  " + strarSlspHistField + " from " + MoveArSlspHisttable + " where " + strCondition + " and typeOeAr='O'";
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveArSlspHisttable + " where " + strCondition + " and typeOeAr='O'";
                        dbcon.ExecuteSql(strSql);
                        strSql = "Insert into arSlspHist  (" + strOeSalesPersonField + ")" +
                                 "select  " + strOeSalesPersonField + " from " + MoveOeSalespersontable + " where " + strCondition + "";
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveOeSalespersontable + " where " + strCondition + "";
                        dbcon.ExecuteSql(strSql);
                        #endregion
                    }
                    else
                    {
                        #region make backup
                        strCondition = "InvNo='" + drw["OrdNo"].ToString() + "'";
                        strCondition2 = "OrdNo='" + drw["OrdNo"].ToString() + "'";
                        if (mblnMakeHistory)
                        {
                            strSql = "Insert into " + MoveOrderHdrtable + " (" + strOeorderhdrfield + ")" +
                                     "select  " + strOeorderhdrfield + " from OeOrderhdr where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from OeOrderhdr where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {
                            strSql = "Insert into " + MoveOrderDtltable + " (" + stroeOrderdtlField + ")" +
                                     "select  " + stroeOrderdtlField + " from Oeorderdtl where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from Oeorderdtl where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveOrderTallytable + " (" + strOeOrdertallyField + ")" +
                                     "select  " + strOeOrdertallyField + " from OeOrdertally where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from OeOrdertally where " + strCondition2;
                        dbcon.ExecuteSql(strSql);

                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveOrderKittable + " (" + strOeOrderKitField + ")" +
                                     "select  " + strOeOrderKitField + " from OeOrderKit where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from OeOrderKit where " + strCondition2;
                        dbcon.ExecuteSql(strSql);

                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveTruchChargetable + " (" + stroeTruckChargeField + ")" +
                                     "select  " + stroeTruckChargeField + " from oeTruckCharge where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from oeTruckCharge where " + strCondition2;
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MovExtraCosttable + " (" + strOeExtraCostField + ")" +
                                     "select  " + strOeExtraCostField + " from OeExtraCost where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from OeExtraCost where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveArSlspHisttable + " (" + strarSlspHistField + ")" +
                                     "select  " + strarSlspHistField + " from arSlspHist where " + strCondition + " and typeOeAr='O'";
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from arSlspHist where " + strCondition + " and typeOeAr='O'";
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveOeSalespersontable + " (" + strOeSalesPersonField + ")" +
                                     "select  " + strOeSalesPersonField + " from OeSalesperson where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from OeSalesperson where " + strCondition2;
                        dbcon.ExecuteSql(strSql);

                        strSql = "delete from tpTranspHdr where  type='O' and DocNo='" + drw["OrdNo"].ToString() + "'";
                        dbcon.ExecuteSql(strSql);



                        #endregion
                    }
                    dbcon.CommitTran();
                }
                catch (Exception e)
                {
                    dbcon.RollbackTranAndClose();
                    MsgBoxEx.Show(e.Message, SysVar.WarnTitle);
                    return false;
                }
                if (progBar != null) progBar.Value++;
                j++;
                strPct = CvtFunc.ToString(j) + "/" + CvtFunc.ToString(dvOrder.Count);
                lblProccedure.Text = strPct;
                lblProccedure.Refresh();
                Application.DoEvents();  

            }
            return true;

        }

        public bool PurgeRM(string strOrderCondition)
        {
            DBAccess dbcon = new DBAccess(false, false);
            string strSql = "";

            const string MoveOrderHdrtable = "rmwkorderhdr_Hist";
            const string MoveOrderDtlOutputtable = "rmWKOrderoutput_Hist";
            const string MoveOrderDtlInputtable = "rmWKOrderinput_Hist";
            const string MoveOrderTallyOuttable = "rmWkOrderOutputTally_Hist";
            const string MoveOrderTallyIntable = "rmWkOrderinputTally_Hist";

            const string MoveOrderOutputKitIntable = "rmWkOrderOutputkit_Hist";
            const string MoveOrderinputkitIntable = "rmWkOrderinputkit_Hist";



            const string MoveTruchChargetable = "rmTruckCharge_Hist";
            const string MovExtraCosttable = "rmExtraCost_Hist";
            const string MovermWkOrderOperationtable = "rmWkOrderOperation_Hist";
            const string MovermWKOrderMultiLevelInputtable = "rmWKOrderMultiLevelInput_Hist";
            const string MovermWkOrderOutputkittable = "rmWkOrderOutputkit_Hist";
            const string MovermWkOrderinputkittable = "rmWkOrderinputkit_Hist";

            if (mblnMakeHistory)
            {

                CreateHistTable(dbcon, "rmwkorderhdr", MoveOrderHdrtable);
                CreateHistTable(dbcon, "rmWKOrderoutput", MoveOrderDtlOutputtable);
                CreateHistTable(dbcon, "rmWKOrderinput", MoveOrderDtlInputtable);
                CreateHistTable(dbcon, "rmWkOrderOutputTally", MoveOrderTallyOuttable);
                CreateHistTable(dbcon, "rmWkOrderinputTally", MoveOrderTallyIntable);
                CreateHistTable(dbcon, "rmTruckCharge", MoveTruchChargetable);
                CreateHistTable(dbcon, "rmExtraCost", MovExtraCosttable);
                CreateHistTable(dbcon, "rmWkOrderOperation", MovermWkOrderOperationtable);
                CreateHistTable(dbcon, "rmWKOrderMultiLevelInput", MovermWKOrderMultiLevelInputtable);

                CreateHistTable(dbcon, "rmWkOrderOutputkit", MovermWkOrderOutputkittable);
                CreateHistTable(dbcon, "rmWkOrderinputkit", MovermWkOrderinputkittable);
            }
            DataView dvOrder = dbcon.GetDataTable("select OrdNo from rmwkorderhdr where posted=1 and " + strOrderCondition).DefaultView;
            if (progBar != null)
            {
                progBar.Maximum = dvOrder.Count + 1;
                progBar.Minimum = 0;
                progBar.Value = 0;
                progBar.Visible = true;
            }

            string strrmorderhdrfield = GetTableFileds(dbcon, "rmwkorderhdr");
            string strRmOutdtlField = GetTableFileds(dbcon, "rmWKOrderoutput");
            string strrmWkOrderOutputTallyField = GetTableFileds(dbcon, "rmWkOrderOutputTally");
            string strrmWkOrderOutputkitField = GetTableFileds(dbcon, "rmWkOrderOutputkit");


            string strRmInputdtlField = GetTableFileds(dbcon, "rmWKOrderinput");
            string strrmWkOrderInputTallyField = GetTableFileds(dbcon, "rmWkOrderInputTally");
            string strrmWkOrderInputkitField = GetTableFileds(dbcon, "rmWkOrderInputkit");



            string strrmTruckChargeField = GetTableFileds(dbcon, "rmTruckCharge");
            string strrmExtraCostField = GetTableFileds(dbcon, "rmExtraCost");
            string strrmWkOrderOperationField = GetTableFileds(dbcon, "rmWkOrderOperation");
            string strrmWKOrderMultiLevelInputField = GetTableFileds(dbcon, "rmWKOrderMultiLevelInput");
            string strCondition, strCondition2, strCondition3;
            string strPct = "0/" + dvOrder.Count.ToString();
            lblProccedure.Text = strPct;
            lblProccedure.Refresh();
            Application.DoEvents();  
            int j = 0;

            foreach (DataRowView drw in dvOrder)
            {
                try
                {
                    dbcon.BeginTran();
                    if (mblnIsRestorehistory)
                    {
                        #region restore hisotry
                        //strCondition = "InvNo='" + drw["OrdNo"].ToString() + "'";
                        //strCondition2 = "OrdNo='" + drw["OrdNo"].ToString() + "'";
                        //strSql = "Insert into oeorderhdr  (" + strrmorderhdrfield + ")" +
                        //         "select  " + strrmorderhdrfield + " from " + MoveOrderHdrtable + " where " + strCondition;
                        //dbcon.ExecuteSql(strSql);
                        //strSql = "delete from " + MoveOrderHdrtable + " where " + strCondition;
                        //dbcon.ExecuteSql(strSql);

                        //strSql = "Insert into OeOrderdtl  (" + strRmOutdtlField + ")" +
                        //         "select  " + strRmOutdtlField + " from " + MoveOrderDtlOutputtable + " where " + strCondition;
                        //dbcon.ExecuteSql(strSql);
                        //strSql = "delete from " + MoveOrderDtlOutputtable + " where " + strCondition;
                        //dbcon.ExecuteSql(strSql);


                        //strSql = "Insert into rmWkOrderOutputTally  (" + strrmWkOrderOutputTallyField + ")" +
                        //         "select  " + strrmWkOrderOutputTallyField + " from " + MoveOrderTallyOuttable + " where " + strCondition2;
                        //dbcon.ExecuteSql(strSql);
                        //strSql = "delete from " + MoveOrderTallyOuttable + " where " + strCondition2;
                        //dbcon.ExecuteSql(strSql);


                        //strSql = "Insert into rmWkOrderOutputkit  (" + strrmWkOrderOutputkitField + ")" +
                        //         "select  " + strrmWkOrderOutputkitField + " from " + MovermWkOrderOutputkittable + " where " + strCondition2;
                        //dbcon.ExecuteSql(strSql);
                        //strSql = "delete from " + MovermWkOrderOutputkittable + " where " + strCondition2;
                        //dbcon.ExecuteSql(strSql);


                        //strSql = "Insert into rmTruckCharge  (" + strrmTruckChargeField + ")" +
                        //         "select  " + strrmTruckChargeField + " from " + MoveTruchChargetable + " where " + strCondition2;
                        //dbcon.ExecuteSql(strSql);
                        //strSql = "delete from " + MoveTruchChargetable + " where " + strCondition2;
                        //dbcon.ExecuteSql(strSql);


                        //strSql = "Insert into rmExtraCost  (" + strrmExtraCostField + ")" +
                        //         "select  " + strrmExtraCostField + " from " + MovExtraCosttable + " where " + strCondition;
                        //dbcon.ExecuteSql(strSql);
                        //strSql = "delete from " + MovExtraCosttable + " where " + strCondition;
                        //dbcon.ExecuteSql(strSql);


                        //strSql = "Insert into rmWkOrderOperation  (" + strrmWkOrderOperationField + ")" +
                        //         "select  " + strrmWkOrderOperationField + " from " + MovermWkOrderOperationtable + " where " + strCondition + " and typeOeAr='O'";
                        //dbcon.ExecuteSql(strSql);
                        //strSql = "delete from " + MovermWkOrderOperationtable + " where " + strCondition + " and typeOeAr='O'";
                        //dbcon.ExecuteSql(strSql);
                        //strSql = "Insert into rmWkOrderOperation  (" + strrmWKOrderMultiLevelInputField + ")" +
                        //         "select  " + strrmWKOrderMultiLevelInputField + " from " + MovermWKOrderMultiLevelInputtable + " where " + strCondition + "";
                        //dbcon.ExecuteSql(strSql);
                        //strSql = "delete from " + MovermWKOrderMultiLevelInputtable + " where " + strCondition + "";
                        //dbcon.ExecuteSql(strSql);
                        #endregion
                    }
                    else
                    {
                        #region make backup
                        strCondition = "OrdNo='" + drw["OrdNo"].ToString() + "'";
                        strCondition2 = "OrdNo='" + drw["OrdNo"].ToString() + "'";
                        if (mblnMakeHistory)
                        {
                            strSql = "Insert into " + MoveOrderHdrtable + " (" + strrmorderhdrfield + ")" +
                                     "select  " + strrmorderhdrfield + " from rmwkorderhdr where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from rmwkorderhdr where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {
                            strSql = "Insert into " + MoveOrderDtlOutputtable + " (" + strRmOutdtlField + ")" +
                                     "select  " + strRmOutdtlField + " from rmWKOrderoutput where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from rmWKOrderoutput where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveOrderTallyOuttable + " (" + strrmWkOrderOutputTallyField + ")" +
                                     "select  " + strrmWkOrderOutputTallyField + " from rmWkOrderOutputTally where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from rmWkOrderOutputTally where " + strCondition2;
                        dbcon.ExecuteSql(strSql);

                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveOrderOutputKitIntable + " (" + strrmWkOrderOutputkitField + ")" +
                                     "select  " + strrmWkOrderOutputkitField + " from rmWkOrderOutputkit where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from rmWkOrderOutputkit where " + strCondition2;
                        dbcon.ExecuteSql(strSql);


                        if (mblnMakeHistory)
                        {
                            strSql = "Insert into " + MoveOrderDtlInputtable + " (" + strRmInputdtlField + ")" +
                                     "select  " + strRmInputdtlField + " from rmWKOrderinput where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from rmWKOrderinput where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveOrderTallyIntable + " (" + strrmWkOrderInputTallyField + ")" +
                                     "select  " + strrmWkOrderInputTallyField + " from rmWkOrderInputTally where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from rmWkOrderInputTally where " + strCondition2;
                        dbcon.ExecuteSql(strSql);

                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveOrderinputkitIntable + " (" + strrmWkOrderInputkitField + ")" +
                                     "select  " + strrmWkOrderInputkitField + " from rmWkOrderInputkit where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from rmWkOrderInputkit where " + strCondition2;
                        dbcon.ExecuteSql(strSql);

                        if (mblnMakeHistory)
                        {
                            strSql = "Insert into " + MoveTruchChargetable + " (" + strrmTruckChargeField + ")" +
                                     "select  " + strrmTruckChargeField + " from rmTruckCharge where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from rmTruckCharge where " + strCondition2;
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MovExtraCosttable + " (" + strrmExtraCostField + ")" +
                                     "select  " + strrmExtraCostField + " from rmExtraCost where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from rmExtraCost where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MovermWkOrderOperationtable + " (" + strrmWkOrderOperationField + ")" +
                                     "select  " + strrmWkOrderOperationField + " from rmWkOrderOperation where " + strCondition + " and typeOeAr='O'";
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from rmWkOrderOperation where " + strCondition + " and typeOeAr='O'";
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MovermWKOrderMultiLevelInputtable + " (" + strrmWKOrderMultiLevelInputField + ")" +
                                     "select  " + strrmWKOrderMultiLevelInputField + " from rmWKOrderMultiLevelInput where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from rmWKOrderMultiLevelInput where " + strCondition2;
                        dbcon.ExecuteSql(strSql);



                        strSql = "delete from tpTranspHdr where  type='B' and DocNo='" + drw["OrdNo"].ToString() + "'";
                        dbcon.ExecuteSql(strSql);

                        #endregion
                    }
                    dbcon.CommitTran();
                }
                catch (Exception e)
                {
                    dbcon.RollbackTranAndClose();
                    MsgBoxEx.Show(e.Message, SysVar.WarnTitle);
                    return false;
                }
                if (progBar != null) progBar.Value++;
                j++;
                strPct = CvtFunc.ToString(j) + "/" + CvtFunc.ToString(dvOrder.Count);
                lblProccedure.Text = strPct;
                lblProccedure.Refresh();
                Application.DoEvents();  

            }
            return true;

        }

        public bool PurgePO(string strOrderCondition)
        {

            DBAccess dbcon = new DBAccess(false, false);
            string strSql = "", strCondition = "", strCondition2="";


            const string MoveOrderHdrtable = "PoOrderhdr_Hist";
            const string MoveOrderDtltable = "PoOrderdtl_Hist";
            const string MoveOrderTallytable = "Potally_Hist";
            const string MoveOrderKittable = "PoKit_Hist";
            const string MoveGoodRecvTable = "PoMultigoodsrecv_Hist";
            const string MoveGoodRecvTallytable = "PoGoodRecvTally_Hist";
            const string MoveGoodRecvKittable = "PoGoodRecvKit_Hist";

            const string MoveTruckChargetable = "poPurchaseTruckCharge_Hist";
            const string MovExtraCosttable = "poPurchaseLandedCostTrailer_Hist";

            const string MoveRecvTruchChargetable = "poTruckCharge_Hist";
            const string MovRecvExtraCosttable = "poLandedCostTrailer_Hist";
            const string MoveTransportTable = "poTransportation_Hist";
            if (mblnMakeHistory)
            {

                CreateHistTable(dbcon, "PoOrderhdr", MoveOrderHdrtable);
                CreateHistTable(dbcon, "PoOrderdtl", MoveOrderDtltable);
                CreateHistTable(dbcon, "PoTally", MoveOrderTallytable);
                CreateHistTable(dbcon, "PoMultigoodsrecv", MoveGoodRecvTable);
                CreateHistTable(dbcon, "PoGoodRecvTally", MoveGoodRecvTallytable);
                CreateHistTable(dbcon, "poPurchaseTruckCharge", MoveTruckChargetable);
                CreateHistTable(dbcon, "poPurchaseLandedCostTrailer", MovExtraCosttable);
                CreateHistTable(dbcon, "poTruckCharge", MoveRecvTruchChargetable);
                CreateHistTable(dbcon, "poLandedCostTrailer", MovRecvExtraCosttable);
                CreateHistTable(dbcon, "poTransportation", MoveTransportTable);
                CreateHistTable(dbcon, "poKit", MoveOrderKittable);
                CreateHistTable(dbcon, "PoGoodRecvKit", MoveGoodRecvKittable);
            }

            DataView dvOrder = dbcon.GetDataTable("select OrdNo from PoOrderhdr where posted=1 and " + strOrderCondition).DefaultView;
            if (progBar != null)
            {
                progBar.Maximum = dvOrder.Count + 1;
                progBar.Minimum = 0;
                progBar.Value  = 0;
                progBar.Visible = true;
            }

           string  strPoorderhdrfield = GetTableFileds(dbcon, "PoOrderhdr");
           string strPoorderdtlfield = GetTableFileds(dbcon, "PoOrderdtl");
           string strPotallyField = GetTableFileds(dbcon, "PoTally");
           string strPoKitField = GetTableFileds(dbcon, "PoKit");

           string strPoMultigoodsRecv = GetTableFileds(dbcon, "PoMultigoodsrecv");
           string strPoGoodRecvTally = GetTableFileds(dbcon, "PoGoodRecvTally");
           string strPoGoodRecvKit = GetTableFileds(dbcon, "PoGoodRecvKit");

           string strPoTruckChargeField = GetTableFileds(dbcon, "poPurchaseTruckCharge");
           string strPoExtraCostField = GetTableFileds(dbcon, "poPurchaseLandedCostTrailer");
           string strPoRecvTruckChargeField = GetTableFileds(dbcon, "poTruckCharge");
           string strPoRecvExtraCostField = GetTableFileds(dbcon, "poLandedCostTrailer");
           string strPoTransportfield = GetTableFileds(dbcon, "poTransportation");

            string strPct = "0/" + dvOrder.Count.ToString();
            lblProccedure.Text = strPct;
            lblProccedure.Refresh();
            Application.DoEvents();  
            int j=0;
            
            foreach (DataRowView drw in dvOrder)
            {
                try
                {
                    dbcon.BeginTran();
                    if (mblnIsRestorehistory)
                    {
                        #region restore hisotry
                        strCondition = "OrdNo='" + drw["OrdNo"].ToString() + "'";
                        strCondition2 = "OrderNo='" + drw["OrdNo"].ToString() + "'";
                        strSql = "Insert into PoOrderhdr  (" + strPoorderhdrfield + ")" +
                                 "select  " + strPoorderhdrfield + " from " + MoveOrderHdrtable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveOrderHdrtable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        strSql = "Insert into PoOrderdtl  (" + strPoorderdtlfield + ")" +
                                 "select  " + strPoorderdtlfield + " from " + MoveOrderDtltable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveOrderDtltable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);


                        strSql = "Insert into PoTally  (" + strPotallyField + ")" +
                                 "select  " + strPotallyField + " from " + MoveOrderTallytable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveOrderTallytable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        strSql = "Insert into PoKit  (" + strPoKitField + ")" +
                                 "select  " + strPoKitField + " from " + MoveOrderKittable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveOrderKittable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);




                        strSql = "Insert into PoMultigoodsrecv  (" + strPoMultigoodsRecv + ")" +
                                 "select  " + strPoMultigoodsRecv + " from " + MoveGoodRecvTable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveGoodRecvTable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);


                        strSql = "Insert into PoGoodRecvTally  (" + strPoGoodRecvTally + ")" +
                                 "select  " + strPoGoodRecvTally + " from " + MoveGoodRecvTallytable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveGoodRecvTallytable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        strSql = "Insert into PoGoodRecvKit  (" + strPoGoodRecvKit + ")" +
                                 "select  " + strPoGoodRecvKit + " from " + MoveGoodRecvKittable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveGoodRecvKittable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);


                        strSql = "Insert into poPurchaseTruckCharge  (" + strPoTruckChargeField + ")" +
                                 "select  " + strPoTruckChargeField + " from " + MoveTruckChargetable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveTruckChargetable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);

                        strSql = "Insert into poPurchaseLandedCostTrailer  (" + strPoExtraCostField + ")" +
                                 "select  " + strPoExtraCostField + " from " + MovExtraCosttable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MovExtraCosttable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);



                        strSql = "Insert into poTruckCharge  (" + strPoRecvTruckChargeField + ")" +
                                 "select  " + strPoRecvTruckChargeField + " from " + MoveRecvTruchChargetable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveRecvTruchChargetable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);


                        strSql = "Insert into poLandedCostTrailer  (" + strPoRecvExtraCostField + ")" +
                                 "select  " + strPoRecvExtraCostField + " from " + MovRecvExtraCosttable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MovRecvExtraCosttable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);


                        strSql = "Insert into poTransportation  (" + strPoTransportfield + ")" +
                                 "select  " + strPoTransportfield + " from " + MoveTransportTable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + MoveTransportTable + " where " + strCondition2;
                        dbcon.ExecuteSql(strSql);
                        #endregion
                    }
                    else
                    {
                        #region make backup

                        strCondition = "OrdNo='" + drw["OrdNo"].ToString() + "'";
                        strCondition2 = "OrderNo='" + drw["OrdNo"].ToString() + "'";
                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveOrderHdrtable + " (" + strPoorderhdrfield + ")" +
                                     "select  " + strPoorderhdrfield + " from PoOrderhdr where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from PoOrderhdr where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveOrderDtltable + " (" + strPoorderdtlfield + ")" +
                                     "select  " + strPoorderdtlfield + " from PoOrderdtl where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from PoOrderdtl where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveOrderTallytable + " (" + strPotallyField + ")" +
                                     "select  " + strPotallyField + " from PoTally where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from PoTally where " + strCondition;
                        dbcon.ExecuteSql(strSql);


                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveOrderKittable + " (" + strPoKitField + ")" +
                                     "select  " + strPoKitField + " from PoKit where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from PoKit where " + strCondition;
                        dbcon.ExecuteSql(strSql);



                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveGoodRecvTable + " (" + strPoMultigoodsRecv + ")" +
                                     "select  " + strPoMultigoodsRecv + " from PoMultigoodsrecv where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from PoMultigoodsrecv where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {


                            strSql = "Insert into " + MoveGoodRecvTallytable + " (" + strPoGoodRecvTally + ")" +
                                     "select  " + strPoGoodRecvTally + " from PoGoodRecvTally where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from PoGoodRecvTally where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveGoodRecvKittable + " (" + strPoGoodRecvKit + ")" +
                                     "select  " + strPoGoodRecvKit + " from PoGoodRecvKit where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from PoGoodRecvKit where " + strCondition;
                        dbcon.ExecuteSql(strSql);


                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveTruckChargetable + " (" + strPoTruckChargeField + ")" +
                                     "select  " + strPoTruckChargeField + " from poPurchaseTruckCharge where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from poPurchaseTruckCharge where " + strCondition2;
                        dbcon.ExecuteSql(strSql);
                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MovExtraCosttable + " (" + strPoExtraCostField + ")" +
                                     "select  " + strPoExtraCostField + " from poPurchaseLandedCostTrailer where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from poPurchaseLandedCostTrailer where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveRecvTruchChargetable + " (" + strPoRecvTruckChargeField + ")" +
                                     "select  " + strPoRecvTruckChargeField + " from poTruckCharge where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from poTruckCharge where " + strCondition2;
                        dbcon.ExecuteSql(strSql);

                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MovRecvExtraCosttable + " (" + strPoRecvExtraCostField + ")" +
                                     "select  " + strPoRecvExtraCostField + " from poLandedCostTrailer where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from poLandedCostTrailer where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        if (mblnMakeHistory)
                        {

                            strSql = "Insert into " + MoveTransportTable + " (" + strPoTransportfield + ")" +
                                     "select  " + strPoTransportfield + " from poTransportation where " + strCondition;
                            dbcon.ExecuteSql(strSql);
                        }
                        strSql = "delete from poTransportation where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        strSql = "delete from tpTranspHdr where  type='U' and DocNo='" + drw["OrdNo"].ToString() + "'";
                        dbcon.ExecuteSql(strSql);

                        #endregion
                    
                    }
                    dbcon.CommitTran();
                }
                catch (Exception e)
                {
                    dbcon.RollbackTranAndClose();
                    MsgBoxEx.Show(e.Message, SysVar.WarnTitle);
                    return false;
                }
                if (progBar != null) progBar.Value++;
                j++;
                strPct = CvtFunc.ToString(j) + "/" + CvtFunc.ToString(dvOrder.Count);
                lblProccedure.Text = strPct;
                lblProccedure.Refresh();
                Application.DoEvents();  

            }

            return true;


        }
        public bool PurgeHistory(string strCondition, string strCutOffDate)
        {
            DBAccess dbcon = new DBAccess(false, false);
            string strSql = "";
            const string HistoryTable = "Iminvhist_Hist";
            const string HistoryTableTag = "IminvhistTag_Hist";
            const string HistoryTableTagRlRw = "IminvhistTagRlRw_Hist";
            string  strCondition2 = "", strCondition3 = "";
            strCondition = strCondition;
            string strParaCond = strCondition;
            string strCurDate = DataFunctions.GetPrdInfo(dbcon, SystemModules.IM, PeriodRange.PeriodCur, PeriodOption.StPrdDate);
            if (mblnMakeHistory)
            {

                CreateHistTable(dbcon, "Iminvhist", HistoryTable);
                CreateHistTable(dbcon, "IminvhistTag", HistoryTableTag);
                CreateHistTable(dbcon, "IminvhistTagRlRw", HistoryTableTagRlRw);
            }

            strSql = "select distinct Itemno,locationno from iminvHist where " + strCondition;
            DataView dvItemLoc = dbcon.GetDataTable(strSql).DefaultView;

            if (progBar != null)
            {
                progBar.Maximum = dvItemLoc.Count + 1;
                progBar.Minimum = 0;
                progBar.Visible = true;
                progBar.Value = 0;
            }



            string strIminvHistField = GetTableFileds(dbcon, "Iminvhist");
            string strIminvHistTagField = GetTableFileds(dbcon, "IminvhistTag");
            string strIminvHistTagRlRwField = GetTableFileds(dbcon, "IminvhistTagRlRw");
            string strPct = "0/" + dvItemLoc.Count.ToString();
            lblProccedure.Text = strPct;
            lblProccedure.Refresh();
            Application.DoEvents();  
            int j = 0;
            foreach (DataRowView drw in dvItemLoc)
            {
                try
                {
                    
                    DataRow drFirst = null, drLast = null;
                    DataRow drItem = dbcon.GetDataFirstRow("select top 1 ItemNo,ProdType, stockUnit,ConversionType,ProdLength,ProdWidth,costunit from iminvmaster where ItemNo='" + drw["ItemNo"].ToString() + "'");
                    DataRow drItemLoc = dbcon.GetDataFirstRow("select top 1 itemno,locationno,Onhand,WtAvgcost from iminvloc where ItemNo='" + drw["ItemNo"].ToString() + "' and LocationNo='" + drw["LocationNo"].ToString() + "'");
                    if (drItem == null || drItemLoc == null) continue;
                    dbcon.BeginTran();
                    if (mblnIsRestorehistory)
                    {
                        #region restore history

                        strCondition = "ItemNO='" + CvtFunc.ToString(drw["ItemNo"]).TrimEnd() + "' and LocationNo='" + drw["LocationNo"].ToString() + "'";
                        strSql = "Insert into Iminvhist  (" + strIminvHistField + ")" +
                                 "select  " + strIminvHistField + " from " + HistoryTable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + HistoryTable + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        strSql = "Insert into IminvhistTag  (" + strIminvHistTagField + ")" +
                                 "select  " + strIminvHistTagField + " from " + HistoryTableTag + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + HistoryTableTag + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);

                        strSql = "Insert into IminvhistTagRlRw  (" + strIminvHistTagRlRwField + ")" +
                                 "select  " + strIminvHistTagRlRwField + " from " + HistoryTableTagRlRw + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        strSql = "delete from " + HistoryTableTagRlRw + " where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        #endregion
                    }
                    else
                    {
                        #region get first / last history record
                        switch (drItem["ProdType"].ToString().TrimEnd().ToUpper())
                        {
                            case "F":
                                strCondition = "ItemNO='" + CvtFunc.ToString(drw["ItemNo"]).TrimEnd() + "' and LocationNo='" + drw["LocationNo"].ToString() + "' and " + strParaCond;
                                strSql = "select top 1 * from iminvhist where  " + strCondition + " order by seqno ";
                                drFirst = dbcon.GetDataFirstRow(strSql);
                                if (drFirst == null) 
                                {
                                    dbcon.CommitTran();
                                    continue;
                                }
                                strSql = "select top 1 docdate,InitialWtAvgCost,FinalWtAvgCost,InitialQtyOnHand,FinalQtyOnHand from iminvhist where " +
                                         " posted=1 and ItemNO='" + CvtFunc.ToString(drw["ItemNo"]).TrimEnd() + "' and LocationNo='" + drw["LocationNo"].ToString() + "' and " + strParaCond +
                                         " order by convert(char(10),transactiondate,111) desc,transactiontime desc";
                                drLast = dbcon.GetDataFirstRow(strSql);
                                strSql = "select isnull(Sum(QtyInstockUnit),0) as Qty,isnull(sum(case when QtyInstockUnit>0 then 1 else -1 end *abs(totalCost)),0) as cost,isnull(sum(TotalSale),0) as Sale from iminvhist where " + strCondition;
                                break;
                            case "P":
                                strCondition = "ItemNO='" + CvtFunc.ToString(drw["ItemNo"]).TrimEnd() + "' and LocationNo='" + drw["LocationNo"].ToString() + "' and " + strParaCond +
                                               " and seqno in (select iminvhistseqnoOut from iminvhisttag where iminvhistseqnoin in (select b.iminvhistseqnoin from iminvhist a inner join iminvhisttag b on a.seqno= b.iminvhistseqnoOut where" +
                                               " a.ItemNO='" + CvtFunc.ToString(drw["ItemNo"]).TrimEnd() + "' and a.LocationNo='" + drw["LocationNo"].ToString() + "' and a.posted=1 group by b.LotNo ,b.iminvhistseqnoin " +
                                               " Having Sum(b.QtyInstockUnit) = 0 ))";
                                DataFunctions.DelTemporyTable(dbcon, "#tmpseqno");
                                strSql = "select seqno into #tmpseqno from iminvhist where " + strCondition;
                                dbcon.ExecuteSql(strSql);
                                strSql = "delete #tmpseqno where seqno in ("
                                + " select a.seqno from  #tmpseqno a"
                                + " inner join iminvhisttag b on a.seqno=b.iminvhistseqnoOut "
                                + " inner join iminvhisttag c on c.iminvhistseqnoin=b.iminvhistseqnoin"
                                + " inner join iminvhist d on d.seqno=c.iminvhistseqnoOut"
                                + " where d.postdate>'" + strCutOffDate + "'"
                                + " )";
                                dbcon.ExecuteSql(strSql);

                                strCondition = "seqno in (select seqno from #tmpseqno)";
                                // DataFunctions.DataBrow(dbcon.GetDataTable("select seqno from #tmpseqno").DefaultView);
                                strSql = "select top 1 * from iminvhist where  seqno in (select top 1 seqno from #tmpSeqno order by seqno) ";
                                drFirst = dbcon.GetDataFirstRow(strSql);
                                if (drFirst == null)
                                {
                                    dbcon.CommitTran();
                                    continue;
                                }


                                strSql = "select top 1 docdate,InitialWtAvgCost,FinalWtAvgCost,InitialQtyOnHand,FinalQtyOnHand from iminvhist where " +
                                         " posted=1 and ItemNO='" + CvtFunc.ToString(drw["ItemNo"]).TrimEnd() + "' and LocationNo='" + drw["LocationNo"].ToString() + "' and " + strParaCond +
                                         " order by convert(char(10),transactiondate,111) desc,transactiontime desc";
                                drLast = dbcon.GetDataFirstRow(strSql);
                                strCondition2 = " iminvhistseqnoOut in (select seqno from #tmpSeqno)";
                                strSql = "select isnull(Sum(QtyInstockUnit),0) as Qty,isnull(sum( case when QtyInstockUnit>0 then 1 else -1 end *abs(totalCost)),0) as cost,isnull(sum(TotalSale),0) as Sale from iminvhist where " + strCondition;
                                break;
                            default:
                                DataFunctions.DelTemporyTable(dbcon, "#tmpSeqno");
                                strCondition = "ItemNO='" + CvtFunc.ToString(drw["ItemNo"]).TrimEnd() + "' and LocationNo='" + drw["LocationNo"].ToString() + "' and " + strParaCond +
                                               " and seqno in (select iminvhistseqnoOut from iminvhisttag where iminvhistseqnoin in (select b.iminvhistseqnoin from iminvhist a inner join iminvhisttag b on a.seqno= b.iminvhistseqnoOut where" +
                                               " a.ItemNO='" + CvtFunc.ToString(drw["ItemNo"]).TrimEnd() + "' and a.LocationNo='" + drw["LocationNo"].ToString() + "' and a.posted=1 group by b.tagno ,b.iminvhistseqnoin " +
                                               " Having Sum(b.QtyInstockUnit) = 0 ))";
                                strSql = "select seqno into #tmpseqno from iminvhist where " + strCondition;
                                dbcon.ExecuteSql(strSql);

                                strSql = "delete #tmpseqno where seqno in ("
                                + " select a.seqno from  #tmpseqno a"
                                + " inner join iminvhisttag b on a.seqno=b.iminvhistseqnoOut "
                                 + " inner join iminvhisttag c on c.iminvhistseqnoin=b.iminvhistseqnoin"
                                + " inner join iminvhist d on d.seqno=c.iminvhistseqnoOut"
                                + " where d.postdate>'" + strCutOffDate + "'"
                                + " )";
                                dbcon.ExecuteSql(strSql);

                                strCondition = "seqno in (select seqno from #tmpseqno)";
                                strSql = "select top 1 * from iminvhist where  seqno in (select top 1 seqno from #tmpSeqno order by seqno) ";
                                drFirst = dbcon.GetDataFirstRow(strSql);
                                if (drFirst == null)
                                {
                                    dbcon.CommitTran();
                                    continue;
                                }


                                strSql = "select top 1 docdate,InitialWtAvgCost,FinalWtAvgCost,InitialQtyOnHand,FinalQtyOnHand from iminvhist where " +
                                         " posted=1 and ItemNO='" + CvtFunc.ToString(drw["ItemNo"]).TrimEnd() + "' and LocationNo='" + drw["LocationNo"].ToString() + "' and " + strParaCond +
                                         " order by convert(char(10),transactiondate,111) desc,transactiontime desc";
                                drLast = dbcon.GetDataFirstRow(strSql);

                                strCondition2 = " iminvhistseqnoOut in (select seqno from #tmpSeqno)";
                                strCondition3 = strCondition2;
                                strSql = "select isnull(Sum(QtyInstockUnit),0) as Qty,isnull(sum(case when QtyInstockUnit>0 then 1 else -1 end *abs(totalCost)),0) as cost,isnull(sum(TotalSale),0) as Sale from iminvhist where " + strCondition;
                                break;

                        }
                        #endregion

                        #region clear old historty and insert into new history
                        DataView dvTotal = dbcon.GetDataTable(strSql).DefaultView;


                        strSql = "Insert into " + HistoryTable + " (" + strIminvHistField + ")" +
                                   " select  " + strIminvHistField + " from iminvhist where " + strCondition;
                        //  DataFunctions.DataBrow(dbcon.GetDataTable("select seqno from #tmpseqno").DefaultView);

                        if (mblnMakeHistory && !BatchInsert(dbcon, "select seqno from iminvhist where " + strCondition + " order by seqno", strSql, "Seqno", "Seqno", 10000))
                        {
                            dbcon.RollbackTranAndClose();
                            return false;
                        }
                        if (drItem["ProdType"].ToString().TrimEnd().ToUpper() == "P")
                        {
                            if (mblnMakeHistory)
                            {
                                strSql = "Insert into " + HistoryTableTag + " (" + strIminvHistTagField + ")" +
                                         " select  " + strIminvHistTagField + " from iminvhisttag where " + strCondition2;
                                dbcon.ExecuteSql(strSql);
                            }
                            strSql = "delete from iminvhisttag where " + strCondition2;
                            dbcon.ExecuteSql(strSql);

                        }
                        else if (drItem["ProdType"].ToString().TrimEnd().ToUpper() != "F" && drItem["ProdType"].ToString().TrimEnd().ToUpper() != "P")
                        {
                            if (mblnMakeHistory)
                            {

                                strSql = "Insert into " + HistoryTableTag + " (" + strIminvHistTagField + ")" +
                                         " select  " + strIminvHistTagField + " from iminvhisttag where " + strCondition2;
                                dbcon.ExecuteSql(strSql);
                            }
                            strSql = "delete from iminvhisttag where " + strCondition2;
                            dbcon.ExecuteSql(strSql);
                            if ((CvtFunc.ToDouble(drItem["ProdLength"]) <= 0 || CvtFunc.ToDouble(drItem["prodwidth"]) <= 0) && drItem["ConversionType"].ToString().TrimEnd().ToUpper() == "D")
                            {
                                strSql = "Insert into " + HistoryTableTagRlRw + " (" + strIminvHistTagRlRwField + ")" +
                                    " select  " + strIminvHistTagRlRwField + " from iminvhisttagrlrw where " + strCondition3;
                                dbcon.ExecuteSql(strSql);
                                strSql = "delete from iminvhisttagrlrw where " + strCondition3;
                                dbcon.ExecuteSql(strSql);
                            }
                        }
                        strSql = "delete from iminvhist where " + strCondition;
                        dbcon.ExecuteSql(strSql);
                        if (drLast != null && drFirst != null)
                        {
                            double dblQty = CvtFunc.ToDouble(dvTotal[0]["Qty"]);
                            if (drItem["ProdType"].ToString().TrimEnd().ToUpper() == "P")
                            {
                                dblQty = DataFunctions.UnitCnv(dbcon, drItem["ItemNo"].ToString(), dblQty, drItem["StockUnit"].ToString(), drFirst["UnitOnQtyEntered"].ToString());
                            }

                            int iSeqNo = CvtFunc.ToInt(DataFunctions.GetNextMaxSeqNo(dbcon, "Iminvhist", "SeqNo"));

                            strSql = "Insert into iminvhist (seqno,ItemNo,LocationNo,DocNo,DocDate,PostDate,transactiondate,TransactionTime,InitialWtAvgCost,FinalWtAvgCost,InitialQtyOnHand,FinalQtyOnHand," +
                                      "QtyInstockUnit,stockUnit,QtyEntered, UnitOnQtyEntered,totalCost,TotalSale,Type,Posted) values (" +
                                      iSeqNo.ToString() + ",'" + drItem["ItemNo"].ToString() + "','" + drw["LocationNo"].ToString() +
                                      "','Purge','" + BaseFunctions.FormatDate(strCutOffDate) + "','" + BaseFunctions.FormatDate(strCutOffDate) + "','" + BaseFunctions.FormatDate(strCutOffDate) + "','" + string.Format("{0:HH:mm:ss}", DateTime.Now) +
                                      "'," + CvtFunc.ToString(drFirst["initialwtavgcost"]) + "," + CvtFunc.ToString(drLast["finalwtavgcost"]) + "," + CvtFunc.ToString(drFirst["InitialQtyOnhand"]) + "," +
                                      CvtFunc.ToString(drLast["FinalQtyOnHand"]) + "," + dvTotal[0]["Qty"].ToString() + ",'" + drItem["StockUnit"].ToString() + "'," + dblQty.ToString() + ",'" + (drItem["ProdType"].ToString().TrimEnd().ToUpper() == "P" ? drFirst["UnitOnQtyEntered"].ToString() : drItem["StockUnit"].ToString()) + "'," + dvTotal[0]["Cost"].ToString() + "," + 0 + ",'Inv',1)";
                            dbcon.ExecuteSql(strSql);
                        }
                        #endregion
                    }
                    dbcon.CommitTran();
                }
                catch (Exception e)
                {
                    dbcon.RollbackTranAndClose();
                    MsgBoxEx.Show(e.Message, SysVar.WarnTitle);
                    return false;
                }
                finally
                {
                    if (progBar != null) progBar.Value++;
                    j++;
                    strPct = CvtFunc.ToString(j) + "/" + CvtFunc.ToString(dvItemLoc.Count);
                }
                lblProccedure.Text = strPct;
                lblProccedure.Refresh();
                Application.DoEvents();  
            }
            return true;

        }

        private string GetTableFileds(DBAccess dbcon,string strTableName)
        {
            string strFields = "";
            DataRow dr = dbcon.GetDataTable("select top 0 * from " + strTableName).NewRow();
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                if (dr.Table.Columns[i].ColumnName.ToUpper().Trim() == "RECID") continue;
                if (dr.Table.Columns[i].ColumnName.ToUpper().Trim() == "RECSTAMP") continue;
                strFields += dr.Table.Columns[i].ColumnName+",";  
            }
            if (strFields.Length > 0) strFields = strFields.Substring(0, strFields.Length - 1);
            return strFields;
        }
        private bool BatchInsert(DBAccess dbcon, string strPageSQL, string strInsertSQL, string strPageKeyField, string strInsertKeyField, int iPageSize)
        {
            string strSql = "";
            int iStKey = 0, iEdKey=0;
            DataView dvPage = dbcon.GetDataTable(strPageSQL).DefaultView;
            if (dvPage.Count == 0) return true;
            
            for (int i = 0; i < dvPage.Count; i += iPageSize)
            {
                iStKey = CvtFunc.ToInt(dvPage[i][strPageKeyField]);
                if (i + iPageSize < dvPage.Count)
                    iEdKey = CvtFunc.ToInt(dvPage[i + iPageSize][strPageKeyField]);
                else
                    iEdKey = CvtFunc.ToInt(dvPage[dvPage.Count-1][strPageKeyField]);
                string strWhere = strInsertKeyField + " >= " + iStKey.ToString() + " And " + strInsertKeyField + (i + iPageSize < dvPage.Count ? " < " : " <= ") + iEdKey.ToString();
                strSql = strInsertSQL.Substring(0,(strInsertSQL.IndexOf(" where ")> 0 ? strInsertSQL.IndexOf(" where "): strInsertSQL.Length))  + BaseFunctions.GetSqlParts(strInsertSQL, SqlOption.SqlPartWhere) + " and " + strWhere;
                dbcon.ExecuteSql(strSql);
              //  DataFunctions.DataBrow(dbcon.GetDataTable("select * from  iminvhist where (seqno in (select seqno from #tmpseqno)) and Seqno >= 5542 And Seqno <= 8274").DefaultView);   
            }
            return true;
        }
        private void CreateHistTable(DBAccess dbcon, string strCurrenTable, string strHistTable)
        {

            string  strSql = "select top 1 * from dbo.sysobjects where id = object_id('[dbo].[" + strHistTable + "]') and OBJECTPROPERTY(id, 'IsUserTable') = 1";
            DataRow dr = dbcon.GetDataFirstRow(strSql);
            if (dr == null)
            {
                dbcon.ExecuteSql("Select top 0  * into " + strHistTable + " from " + strCurrenTable);
                return;
            }
            else
            {
                dbcon.BeginTran(); 
                DataFunctions.DelTemporyTable(dbcon, "Tmpxx_" + strHistTable);
                dbcon.ExecuteSql("Select top 0  * into " + "Tmpxx_" + strHistTable + " from " + strCurrenTable);
                string strFileds = GetTableFileds(dbcon, strHistTable);
                strSql = "Insert into Tmpxx_" + strHistTable + "(" + strFileds + ") select " + strFileds + " from " + strHistTable;
                dbcon.ExecuteSql(strSql);
                strSql = "DROP TABLE dbo." +strHistTable ;
                dbcon.ExecuteSql(strSql);
                strSql = "sp_rename 'Tmpxx_" + strHistTable + "', '" + strHistTable + "'";
                dbcon.ExecuteSql(strSql);
                dbcon.CommitTran();  
            }
            
        }
    }
}
